/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package server.baza;

import domen.OpstiDomenskiObjekat;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.logging.Level;
import java.util.logging.Logger;
import server.util.PropsFile;

/**
 *
 * @author Ivan Ivic
 */
public class DataBaseBroker {
    private static DataBaseBroker dbb;
    private Connection konekcija;
    private int signal;

    private DataBaseBroker()
    {
        //ucitajDriver("sun.jdbc.odbc.JdbcOdbcDriver");
        //uspostaviKonekciju("jdbc:odbc:Baza");
    }

    private void ucitajDriver(String nazivDrivera) throws ClassNotFoundException
    {
        Class.forName(nazivDrivera);
        System.out.println("Driver ucitan");
    }

    private void uspostaviKonekciju(String url) throws SQLException
    {
        konekcija = DriverManager.getConnection(url);
        System.out.println("Konekcija uspostavljena");

    }

    public int poveziSeSaBazom()
    {
        try {
            String filename = System.getProperty("user.dir") + File.separator + "db.props";
            String driver  = PropsFile.getProps(filename).getDriver();
            String url = PropsFile.getProps(filename).getUrl();

            ucitajDriver(driver);
            uspostaviKonekciju(url);
            otvoriTransakciju();
            return 9000;
        } catch (FileNotFoundException ex) {
            ex.printStackTrace();
            return 9001;
        } catch (IOException ex) {
            ex.printStackTrace();
            return 9002;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return 9003;
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
            return 9004;
        }
        
    }

    public int zatvoriKonekciju()
    {
        try {
            konekcija.close();
            System.out.println("Konekcija zatvorena");
            return 9005;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return 9006;
        }
        
    }

    private void otvoriTransakciju() throws SQLException
    {
        konekcija.setAutoCommit(false);
    }

    public int commitTransakcije()
    {
        try {
            konekcija.commit();
            return 9007;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return 9008;
        }        
    }

    public int rollbackTransakcije()
    {
        try {
            konekcija.rollback();
            return 9009;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return 9010;
        }       
    }

    public int kreiraj(OpstiDomenskiObjekat odo)
    {
        String upit = "INSERT INTO " + odo.vratiNazivTabele() + " VALUES(" + odo.vratiVrednostiZaInsert() + ")";
        try {
            Statement st = konekcija.createStatement();
            st.executeUpdate(upit);
            System.out.println("Insertovano " + odo.toString());
            return 9011;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return 9012;
        }
    }

    public int sacuvajObjekat(OpstiDomenskiObjekat odo)
    {
        String upit = "UPDATE " + odo.vratiNazivTabele() + " SET" + odo.postaviVrednostiAtributa() + " WHERE " + odo.vratiJedinstveniIdentifikator() + "=" + odo.vratiUslovZaIndentifikacijuUBazi();
        try {
            Statement st = konekcija.createStatement();
            st.executeUpdate(upit);
            return 9013;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return 9014;
        }
    }

    /*
    public int sacuvajObjekat2(OpstiDomenskiObjekat odo)
    {
        try
        {
            obrisi(odo);
            kreiraj(odo);
            return 9013;
        }
        catch(Exception e)
        {
            return 9014;
        }
    }
     * 
     */


    public int obrisi(OpstiDomenskiObjekat odo)
    {
        String upit = "DELETE * FROM " + odo.vratiNazivTabele() + " WHERE " + odo.vratiJedinstveniIdentifikator() + "=" + odo.vratiUslovZaIndentifikacijuUBazi();
        Statement st;
        try {
            st = konekcija.createStatement();
            st.executeUpdate(upit);
            return 9015;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return 9016;
        }
    }

    // 9023, 9024
    public LinkedList<OpstiDomenskiObjekat> vratiSve(OpstiDomenskiObjekat odo)
    {
        try {
            String upit = "SELECT * FROM " + odo.vratiNazivTabele();
            Statement st = konekcija.createStatement();
            ResultSet rs = st.executeQuery(upit);
            signal = 9023;
            return odo.konvertujResultSetUListu(rs);
        } catch (SQLException ex) {
            Logger.getLogger(DataBaseBroker.class.getName()).log(Level.SEVERE, null, ex);
            signal = 9024;
            return null;
        }
    }

    // 9017, 9018
    public OpstiDomenskiObjekat vrati(OpstiDomenskiObjekat odo)
    {
        try {
            String upit = "SELECT * FROM " + odo.vratiNazivTabele() + " WHERE " + odo.vratiJedinstveniIdentifikator() + "=" + odo.vratiUslovZaIndentifikacijuUBazi();
            Statement st = konekcija.createStatement();
            ResultSet rs = st.executeQuery(upit);
            LinkedList<OpstiDomenskiObjekat> l = odo.konvertujResultSetUListu(rs);
            signal = 9017;
            if (!l.isEmpty()) {
                return l.getFirst();
            } else {
                return null;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataBaseBroker.class.getName()).log(Level.SEVERE, null, ex);
            signal = 9018;
            return null;
        }
    }

    // 9015, 9016
    public LinkedList<OpstiDomenskiObjekat> pronadji(OpstiDomenskiObjekat odo) 
    {
        try {
            String upit = "SELECT * FROM " + odo.vratiNazivTabele() + " WHERE " + odo.vratiUslovPretrage();
            Statement st = konekcija.createStatement();
            ResultSet rs = st.executeQuery(upit);
            signal = 9015;
            return odo.konvertujResultSetUListu(rs);
        } catch (SQLException ex) {
            Logger.getLogger(DataBaseBroker.class.getName()).log(Level.SEVERE, null, ex);
            signal = 9016;
            return null;
        }
    }

    public static DataBaseBroker getDbb() {
        if (dbb == null)
            dbb = new DataBaseBroker();
        return dbb;
    }

    public int vratiIDPoslednjegSloga(OpstiDomenskiObjekat odo)
    {
        int idPoslednjegSloga = -1;
        
        LinkedList<OpstiDomenskiObjekat> listaSlogova = vratiSve(odo);
        odo = listaSlogova.getLast();
        idPoslednjegSloga = Integer.parseInt(odo.vratiUslovZaIndentifikacijuUBazi());
        
        return idPoslednjegSloga;
    }

    // 9019, 9020
    public int sacuvajUDodatnojTabeli(OpstiDomenskiObjekat odo)
    {
        try {
            String upit = "DELETE * FROM " + odo.vratiNazivDodatneTabele() + " WHERE " + odo.vratiJedinstveniIdentifikator() + "=" + odo.vratiUslovZaIndentifikacijuUBazi();
            Statement st = konekcija.createStatement();
            st.executeUpdate(upit);

            upit = "INSERT INTO " + odo.vratiNazivDodatneTabele() + " VALUES(" + odo.vratiVrednostiZaInsertDodatneTabele() + ")";
            st = konekcija.createStatement();
            st.executeUpdate(upit);
            return 9019;
        } catch (SQLException ex) {
            Logger.getLogger(DataBaseBroker.class.getName()).log(Level.SEVERE, null, ex);
            return 9020;
        }
    }

    // 9021, 9022
    public int prebroj(OpstiDomenskiObjekat odo)
    {
        try {
            int i = 0;
            String upit = "SELECT COUNT(*) AS rowcount FROM " + odo.vratiNazivTabele() + " WHERE " + odo.vratiUslovPretrage();
            Statement st = konekcija.createStatement();
            ResultSet rs = st.executeQuery(upit);
            rs.next();
            i = rs.getInt("rowcount");
            signal = 9021;
            return i;
        } catch (SQLException ex) {
            Logger.getLogger(DataBaseBroker.class.getName()).log(Level.SEVERE, null, ex);
            signal = 9022;
            return 0;
        }
    }

    // 9025, 9026
    public OpstiDomenskiObjekat vratiIzDodatneTabele(OpstiDomenskiObjekat odo)
    {
        try
        {
            String upit = "SELECT * FROM " + odo.vratiNazivDodatneTabele() + " WHERE " + odo.vratiJedinstveniIdentifikator() + "=" + odo.vratiUslovZaIndentifikacijuUBazi();
            Statement st = konekcija.createStatement();
            ResultSet rs = st.executeQuery(upit);
            signal = 9025;
            return odo.konvertujResultSetUObjekat(rs);
        }
        catch (SQLException e)
        {
            e.printStackTrace();
            signal = 9026;
            return null;
        }
    }

    public int getSignal() {
        return signal;
    }

    public void setSignal(int signal) {
        this.signal = signal;
    }
}
